/*
 *  Copyright 2008-2010 www.jrails.org
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 */

package org.rails.core.db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;

import javax.sql.DataSource;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.rails.core.model.ActiveRecord;
import org.rails.core.sql.SQLDeleteBuilder;
import org.rails.core.sql.SQLFactory;
import org.rails.core.sql.SQLInsertBuilder;
import org.rails.core.sql.SQLManager;
import org.rails.core.sql.SQLQueryBuilder;
import org.rails.core.sql.SQLUpdateBuilder;
import org.rails.core.util.Utils;

/**
 * SQL操作辅助类
 * 
 * @author Jack Ye
 * 
 */
public final class SQLHelper {

	private final Logger logger = Logger.getLogger(this.getClass());

	private DataSource dataSource;

	private StatementHelper statementHelper;

	private Connection connection;

	private int maxResult = 0;

	/**
	 * 根据数据源构造新的 SQLHelper。
	 * 
	 * @param dataSource
	 */
	public SQLHelper(DataSource dataSource) {
		super();
		this.dataSource = dataSource;
	}

	public List<Map<String, Object>> executeQuery(SQLManager sqlManager)
			throws SQLException {
		final SQLFactory sf = new SQLFactory(sqlManager);
		final SQLQueryBuilder sqb = sf.getQueryBuilder();
		logger.debug("Query SQL : " + sqb.getSql());
		logger.debug("SQL Parameter : " + sqb.getParams());
		try {
			open();
			statementHelper = new StatementHelper(connection, sqb.getSql(),
					sqb.getParams());
			statementHelper.setMaxRows(sqb.getMaxResult());
			return statementHelper.executeQuery();
		} finally {
			close();
		}
	}

	public List<Map<String, Object>> executeQuery(String sql,
			List<Object> params) throws SQLException {
		logger.debug("Query SQL : " + sql);
		logger.debug("SQL Parameter : " + params);
		try {
			open();
			statementHelper = new StatementHelper(connection, sql, params);
			return statementHelper.executeQuery();
		} finally {
			close();
		}
	}

	public List<Map<String, Object>> executeQuery(String sql, Object[] params)
			throws SQLException {
		List<Object> p = Arrays.asList(params);
		logger.debug("Query SQL : " + sql);
		logger.debug("SQL Parameter : " + p);
		try {
			open();
			statementHelper = new StatementHelper(connection, sql, p);
			return statementHelper.executeQuery();
		} finally {
			close();
		}
	}

	public List<String> getColumns(String tableName) throws SQLException {
		logger.debug("Get " + tableName + " column names");
		try {
			open();
			statementHelper = new StatementHelper(connection, Utils.format(
					"SELECT * FROM {0} WHERE 1 = 0", tableName), null);
			return statementHelper.getColumns();
		} finally {
			close();
		}
	}
	
	public List<String[]> getColumnMaps(String tableName) throws SQLException{
		logger.debug("Get " + tableName + " column map names");
		try {
			open();
			statementHelper = new StatementHelper(connection, Utils.format(
					"SELECT * FROM {0} WHERE 1 = 0", tableName), null);
			return statementHelper.getColumnMaps();
		} finally {
			close();
		}
	}

	public int executeCreate(SQLManager sqlManager) throws SQLException {
		final SQLFactory sf = new SQLFactory(sqlManager);
		SQLInsertBuilder sib = sf.getInsertBuilder();
		logger.debug("Execute SQL : " + sib.getSql());
		logger.debug("SQL Parameter : " + sib.getParams());
		try {
			open();
			statementHelper = new StatementHelper(connection, sib.getSql(),
					sib.getParams());
			return statementHelper.executeCreate(sib.getRecord());
		} finally {
			close();
		}
	}

	public int executeUpdate(SQLManager sqlManager) throws SQLException {
		final SQLFactory sf = new SQLFactory(sqlManager);
		final SQLUpdateBuilder sub = sf.getUpdateBuilder();
		logger.debug("Execute SQL : " + sub.getSql());
		logger.debug("SQL Parameter : " + sub.getParams());
		try {
			open();
			statementHelper = new StatementHelper(connection, sub.getSql(),
					sub.getParams());
			return statementHelper.executeUpdate();
		} finally {
			close();
		}
	}

	public int executeUpdate(String sql, List<Object> params)
			throws SQLException {
		logger.debug("Execute SQL : " + sql);
		logger.debug("SQL Parameter : " + params);
		try {
			open();
			statementHelper = new StatementHelper(connection, sql, params);
			return statementHelper.executeUpdate();
		} finally {
			close();
		}
	}

	public int executeUpdate(String sql, Object[] params) throws SQLException {
		List<Object> p = Arrays.asList(params);
		logger.debug("Execute SQL : " + sql);
		logger.debug("SQL Parameter : " + p);
		try {
			open();
			statementHelper = new StatementHelper(connection, sql, p);
			return statementHelper.executeUpdate();
		} finally {
			close();
		}
	}

	public int executeDelete(SQLManager sqlManager) throws SQLException {
		final SQLFactory sf = new SQLFactory(sqlManager);
		final SQLDeleteBuilder sdb = sf.getDeleteBuilder();
		logger.debug("Execute SQL : " + sdb.getSql());
		logger.debug("SQL Parameter : " + sdb.getParams());
		try {
			open();
			statementHelper = new StatementHelper(connection, sdb.getSql(),
					sdb.getParams());
			return statementHelper.executeUpdate();
		} finally {
			close();
		}
	}

	public int[] executeInsertBatch(List<? extends ActiveRecord> records)
			throws SQLException {
		try {
			open();
			statementHelper = new StatementHelper(connection);
			return statementHelper.executeInsertBatch(records);
		} finally {
			close();
		}
	}

	public int[] executeUpdateBatch(List<? extends ActiveRecord> records)
			throws SQLException {
		try {
			open();
			statementHelper = new StatementHelper(connection);
			return statementHelper.executeUpdateBatch(records);
		} finally {
			close();
		}
	}

	public int[] executeDeleteBatch(List<? extends ActiveRecord> records)
			throws SQLException {
		try {
			open();
			statementHelper = new StatementHelper(connection);
			return statementHelper.executeDeleteBatch(records);
		} finally {
			close();
		}
	}

	public int getMaxResult() {
		return maxResult;
	}

	public void setMaxResult(int maxResult) {
		this.maxResult = maxResult;
	}

	protected void open() throws SQLException {
		logger.info("Open Connection");
		connection = dataSource.getConnection();
		connection.setAutoCommit(true);
	}

	protected void close() throws SQLException {
		logger.info("Close Connection");
		if (statementHelper != null)
			statementHelper.close();
		if (connection != null)
			connection.close();
	}

	public Connection getConnection() {
		return connection;
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public static List<Map<String, Object>> executeProcResult(
			DataSource dataSource, String procName, Object[] procParams,
			int...outIndex) throws SQLException {
		final List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
		Map<String, Object> row = null;
		ResultSetMetaData rsmd = null;
		ResultSet result = null;
		CallableStatement cstm = null;
		final Connection conn = dataSource.getConnection();
		try {
			cstm = conn.prepareCall("{CALL " + procName + "}");
			if (procParams != null) {
				for (int i = 0; i < procParams.length; i++) {
					cstm.setObject(i + 1, procParams[i]);
				}
			}
			if (outIndex != null) {
				for (int i = 0; i < outIndex.length; i++) {
					cstm.registerOutParameter(outIndex[i] + 1, Types.JAVA_OBJECT);
				}
			}
			result = cstm.executeQuery();
			rsmd = result.getMetaData();
			int c = rsmd.getColumnCount();
			while (result.next()) {
				row = new HashMap<String, Object>();
				for (int i = 0; i < c; i++) {
					row.put(rsmd.getColumnName(i + 1).toLowerCase(),
							result.getObject(i + 1));
				}
				rows.add(row);
			}
			if (outIndex != null) {
				for(int i = 0;i < outIndex.length;i++){
					procParams[outIndex[i]] = cstm.getObject(outIndex[i] + 1);
				}
			}
			return rows;
		} finally {
			if(result != null)
				result.close();
			if(cstm != null)
				cstm.close();
			if(conn != null)
				conn.close();
		}
	}
	
	public static void executeProc(DataSource dataSource, String procName, Object[] procParams,
			int...outIndex) throws SQLException {
		CallableStatement cstm = null;
		final Connection conn = dataSource.getConnection();
		try {
			cstm = conn.prepareCall("{CALL " + procName + "}");
			if (procParams != null) {
				for (int i = 0; i < procParams.length; i++) {
					cstm.setObject(i + 1, procParams[i]);
				}
			}
			if (outIndex != null) {
				for (int i = 0; i < outIndex.length; i++) {
					cstm.registerOutParameter(outIndex[i] + 1, Types.VARCHAR);
				}
			}
			cstm.execute();
			if (outIndex != null) {
				for(int i = 0;i < outIndex.length;i++){
					procParams[outIndex[i]] = cstm.getObject(outIndex[i] + 1);
				}
			}
		} finally {
			if(cstm != null)
				cstm.close();
			if(conn != null)
				conn.close();
		}
	}

}
